package org.jeecgframework.web.service.impl;

import java.io.IOException;
import java.io.OutputStream;
import java.util.*;
import java.text.SimpleDateFormat;
import java.text.DecimalFormat;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.hibernate.Criteria;
import org.hibernate.criterion.Restrictions;
import org.jeecgframework.core.common.model.json.AjaxJson;
import org.jeecgframework.core.common.model.json.ComboTree;
import org.jeecgframework.core.common.model.json.Highchart;
import org.jeecgframework.core.common.service.impl.CommonServiceImpl;
import org.jeecgframework.core.util.ResourceUtil;
import org.jeecgframework.core.util.StringUtil;
import org.jeecgframework.core.util.oConvertUtils;
import org.jeecgframework.tag.vo.easyui.ComboTreeModel;
import org.jeecgframework.web.service.ZyCommonService;
import org.jeecgframework.web.system.pojo.base.*;
import org.jeecgframework.web.system.service.SystemService;
import org.jeecgframework.web.util.SendMessage;
import org.jeecgframework.web.util.StaticValue;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;


@Service("zyCommonService")
@Transactional
public class ZyCommonServiceImpl extends CommonServiceImpl implements ZyCommonService {

    @Autowired
    private SystemService systemService;

    public List<ZyCinemaEntity> queryZyCinema() {
        return super.getList(ZyCinemaEntity.class);
    }

    public TSType queryTsType(String tsTypeGroupCode, String tsTypeCode) {
        if (!StringUtil.isNotEmpty(tsTypeGroupCode) && !StringUtil.isNotEmpty(tsTypeCode)) {
            return null;
        }
        TSType tt = null;
        List<TSType> tsTypes = super.findHql("FROM TSType where TSTypegroup.typegroupcode =? and typecode =?", tsTypeGroupCode, tsTypeCode);
        if (null != tsTypes && tsTypes.size() > 0) {
            tt = tsTypes.get(0);
        }
//		List<TSTypegroup> tsTypegroupList = super.findByProperty(TSTypegroup.class, "typegroupcode", tsTypeGroupCode);
//		if(tsTypegroupList != null && tsTypegroupList.size() != 0){
//			List<TSType> tsTypeList = tsTypegroupList.get(0).getTSTypes();
//			for (TSType tsType : tsTypeList) {
//				if(tsType.getTypecode().equals(tsTypeCode)){
//					tt = tsType;
//					break;
//				}
//			}
//		}
        return tt;
    }

    /**
     * @param orgCode  组织 code
     * @param roleCode 权限 code
     * @return 用户
     * @author xiul
     * 2016-4-11 上午11:24:35
     * Description 根据orgcode 和rolecode 查询用户
     */
    private List<Map<String, Object>> queryUsersByOrgCodeAndRoleCode(String orgCode, String roleCode) {
        if (!StringUtil.isNotEmpty(orgCode) || !StringUtil.isNotEmpty(roleCode)) {
            return null;
        }
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT");
        sql.append(" bu.id, bu.username, u.email ");//2016-11-1 xiu 新加了一个查询字段 email 和 left join t_s_user ，如出现异常请删除
//		sql.append("dp.org_code, dp.departname,");
//		sql.append("ro.rolecode, ro.rolename");
        sql.append(" FROM");
        sql.append(" t_s_base_user bu");
        sql.append(" LEFT JOIN t_s_user u on u.id = bu.ID");
        sql.append(" LEFT JOIN t_s_user_org uo on uo.user_id = bu.ID");
        sql.append(" LEFT JOIN t_s_depart dp on dp.ID = uo.org_id");
        sql.append(" LEFT JOIN t_s_role_user ru on ru.userid = bu.ID");
        sql.append(" LEFT JOIN t_s_role ro on ro.ID = ru.roleid");
        sql.append(" WHERE");
        sql.append(" dp.org_code = '").append(orgCode).append("' ");
        sql.append(" AND dp.logic_delete = 0 ");
        sql.append(" AND ro.rolecode = '").append(roleCode).append("' ");
        return this.findForJdbc(sql.toString());
    }


    public List<Map<String, Object>> queryUserNamesListByOrgCodeAndRoleCode(String orgCode, String roleCode) {
        return queryUsersByOrgCodeAndRoleCode(orgCode, roleCode);
    }

    public String queryUserNamesByOrgCodeAndRoleCode(String orgCode, String roleCode) {
        List<Map<String, Object>> results = queryUsersByOrgCodeAndRoleCode(orgCode, roleCode);
        StringBuffer sb = new StringBuffer();
        if (results.size() != 0) {
            for (Map<String, Object> map : results) {
                sb.append(map.get("username") + ",");
            }
        }
        String userNames = sb.toString();
        if (StringUtil.isNotEmpty(userNames)) {
            userNames = userNames.substring(0, userNames.length() - 1);
        }
        return userNames;
    }

    public List<TSDepart> queryOrgByOrgTypeAndBusScope(String orgType, String busScope) {
        List<TSDepart> tsDeparts = new ArrayList<TSDepart>();
        if (StringUtil.isNotEmpty(orgType) || StringUtil.isNotEmpty(orgType)) {
            Criteria c = getSession().createCriteria(TSDepart.class);
            c.add(Restrictions.eq("orgType", orgType));
            c.add(Restrictions.like("busScope", "%" + busScope + "%"));
            tsDeparts = (List<TSDepart>) c.list();
        }
        return tsDeparts;
    }

    //	@Override
    public List<ZyContactsEntity> queryUsersByConditions(String orgCode, String roleCode, String busScope, String ctPosition) {
        List<ZyContactsEntity> ctContacters = new ArrayList<ZyContactsEntity>();
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ");
        sql.append(" ct.id,ct.base_id,ct.`name`,ct.cellphone1,ct.cellphone2,ct.telephone,ct.gender,ct.state,ct.email,ct.position,ct.source,ct.con_business,ct.type,eng.authentication ");
        sql.append(" FROM ");
        sql.append(" t_s_base_user bu ");
        sql.append(" LEFT JOIN zy_contacts ct on ct.base_id = bu.ID ");
        sql.append(" LEFT JOIN t_s_user_org uo on uo.user_id = bu.ID ");
        sql.append(" LEFT JOIN t_s_depart dp on dp.ID = uo.org_id ");
        sql.append(" LEFT JOIN t_s_role_user ru on ru.userid = bu.ID ");
        sql.append(" LEFT JOIN t_s_role ro on ro.ID = ru.roleid  ");
        sql.append(" LEFT JOIN zy_engineer eng on eng.TS_BASE_USER_ID =bu.ID  ");
        sql.append(" WHERE ");
        sql.append(" dp.org_code = '").append(orgCode).append("' ");
        sql.append(" AND dp.logic_delete = 0 ");
        sql.append(" AND ro.rolecode = '").append(roleCode).append("' ");
        sql.append(" AND ct.position = '").append(ctPosition).append("' ");
        sql.append(" AND ct.is_delete = '0' ");
        sql.append(" AND ct.state = '").append(StaticValue.DIC_ENGINEER_STATE_ONJOB).append("' ");
        sql.append(" AND ct.con_business LIKE '%").append(busScope).append("%' ");//根据bug3673 增加条件 联系人业务包含 巡检 2016-12-08
        sql.append(" AND dp.bus_scope LIKE '%").append(busScope).append("%' ");
        List<Map<String, Object>> results = super.findForJdbc(sql.toString());
        if (results != null) {
            for (Map<String, Object> map : results) {
                ZyContactsEntity contacter = new ZyContactsEntity();
                contacter.setId(map.get("id") != null ? map.get("id").toString() : "");
                TSBaseUser baseUser = new TSBaseUser();
                baseUser.setId(map.get("base_id") != null ? map.get("base_id").toString() : "");
                contacter.setTsBaseUser(baseUser);
                contacter.setName(map.get("name") != null ? map.get("name").toString() : "");
                contacter.setCellphone1(map.get("cellphone1") != null ? map.get("cellphone1").toString() : "");
                contacter.setCellphone2(map.get("cellphone2") != null ? map.get("cellphone2").toString() : "");
                contacter.setTelephone(map.get("telephone") != null ? map.get("telephone").toString() : "");
                if (map.get("gender") != null) {
                    contacter.setGender(map.get("gender").toString().equals("1"));
                }
                contacter.setState(map.get("state") != null ? map.get("state").toString() : "");
                contacter.setEmail(map.get("email") != null ? map.get("email").toString() : "");
                contacter.setPosition(map.get("position") != null ? map.get("position").toString() : "");
                contacter.setSource(map.get("source") != null ? map.get("source").toString() : "");
                contacter.setConBusiness(map.get("con_business") != null ? map.get("con_business").toString() : "");
                contacter.setType(map.get("type") != null ? map.get("type").toString() : "");
                contacter.setAuthenticationUser(map.get("authentication") != null ? map.get("authentication").toString() : "");
                ctContacters.add(contacter);
            }
        }
        return ctContacters;
    }

    private List<Map<String, Object>> queryIpsByYearAndStates(Integer year, List states) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ");
        sql.append("    ip.processs_id AS processInsId ");
        sql.append(" FROM ");
        sql.append("    zy_inspection_plan ip");
        sql.append(" WHERE ");
        sql.append("    YEAR (ip.ip_plan_date) = '").append(year).append("' ");
        sql.append(" AND ip.state in (");
        for (int i = 0; i < states.size(); i++) {
            if (i == states.size() - 1) {
                sql.append("'" + states.get(i) + "'");
            } else {
                sql.append("'" + states.get(i) + "',");
            }
        }
        sql.append(")");
        return super.findForJdbc(sql.toString());
    }

    public List<Highchart> queryIpsHighChartData(String type) {
        List<Highchart> datas = new ArrayList<Highchart>();
        Map<String, Object> dataMap = new HashMap<String, Object>();
        List dataList = new ArrayList();
        Calendar cld = Calendar.getInstance();
        Integer year = cld.get(Calendar.YEAR);
        Highchart highChart = new Highchart();
        highChart.setName("本年度巡检进度");
        if (StringUtil.isNotEmpty(type)) {
            highChart.setType("column");
        } else {
            highChart.setType("pie");
        }
        List<String> states = new ArrayList<String>();
        /*------未派单 ↓↓↓↓↓↓↓↓↓↓↓↓↓------*/
        states.clear();
        states.add(StaticValue.DIC_PLAN_STATE_TODO);
        states.add(StaticValue.DIC_PLAN_STATE_THIRDPARTY_REFUSED);
        dataMap = new HashMap<String, Object>();
        dataMap.put("name", "未派单");
        dataMap.put("color", "#113965");
        dataMap.put("y", queryIpsByYearAndStates(year, states).size());
        dataList.add(dataMap);
        /*------已派单 ↓↓↓↓↓↓↓↓↓↓↓↓↓------*/
        states.clear();
        states.add(StaticValue.DIC_PLAN_STATE_SEND_TO_THIRDPARTY);//巴可主管已分派
        states.add(StaticValue.DIC_PLAN_STATE_THIRDPARTY_ASSIGNED);//第三方已分派
        states.add(StaticValue.DIC_PLAN_STATE_AREA_ENGINEER_REFUSED);//区域工程师拒绝
        Integer countAllreadySend = queryIpsByYearAndStates(year, states).size();
        dataMap = new HashMap<String, Object>();
        dataMap.put("name", "已派单");
        dataMap.put("color", "#7CB5EC");
        dataMap.put("y", countAllreadySend);
        dataList.add(dataMap);
        /*------已分配工程师 ↓↓↓↓↓↓↓↓↓↓↓↓↓------*/
        states.clear();
        states.add(StaticValue.DIC_PLAN_STATE_AREA_ENGINEER_ASSIGNED);//区域工程师已分派
        states.add(StaticValue.DIC_PLAN_STATE_ENGINEER_ASSIGNED);//工程师已接受
        states.add(StaticValue.DIC_PLAN_STATE_ENGINEER_REFUSED);//工程师拒绝
        states.add(StaticValue.DIC_PLAN_STATE_ENGINEER_IN_IMPLEMENT);//工程师实施中
        dataMap = new HashMap<String, Object>();
        dataMap.put("name", "已分配工程师");
        dataMap.put("color", "#90ED7D");
        dataMap.put("y", queryIpsByYearAndStates(year, states).size());
        dataList.add(dataMap);
        /*------已完工 ↓↓↓↓↓↓↓↓↓↓↓↓↓------*/
        states.clear();
        states.add(StaticValue.DIC_PLAN_STATE_INSPECTION_COMPLETE);
        dataMap = new HashMap<String, Object>();
        dataMap.put("name", "已完工");
        dataMap.put("color", "#F7A35C");
        dataMap.put("y", queryIpsByYearAndStates(year, states).size());
        dataList.add(dataMap);
        /*------已取消 ↓↓↓↓↓↓↓↓↓↓↓↓↓------*/
        states.clear();
        states.add(StaticValue.DIC_PLAN_STATE_INSPECTION_CANCEL);
        dataMap = new HashMap<String, Object>();
        dataMap.put("name", "已取消");
        dataMap.put("color", "#8085E9");
        dataMap.put("y", queryIpsByYearAndStates(year, states).size());
        dataList.add(dataMap);

        highChart.setData(dataList);
        datas.add(highChart);
        return datas;
    }

    public List<String> queryYearsInIp() {
        List<String> years = new ArrayList<String>();
        List<Map<String, Object>> mapList = findForJdbc("SELECT YEAR(ip_plan_date) AS _year FROM zy_inspection_plan GROUP BY _year ORDER BY _year DESC");
        for (Map<String, Object> map : mapList) {
            years.add(map.get("_year").toString());
        }
        return years;
    }

    public List<String> queryYearsInIpForRepair() {
        List<String> years = new ArrayList<String>();
        List<Map<String, Object>> mapList = findForJdbc("SELECT YEAR(se.create_date) AS _year FROM cbs_service se LEFT JOIN cbs_service_type st ON se.service_type_1 = st.id  where st.`name` = '故障维修' GROUP BY _year ORDER BY _year DESC");
        for (Map<String, Object> map : mapList) {
            years.add(map.get("_year").toString());
        }
        return years;
    }

    @Override
    public Boolean isAreaEngineer(String id) {
        List<TSRoleUser> tsRoleUsers = super.findByProperty(TSRoleUser.class, "TSUser.id", id);
        String areaEngineerRoleCode = StaticValue.RULE_CODE_AREA_ENGINEER;
        for (TSRoleUser tsRoleUser : tsRoleUsers) {
            if (tsRoleUser.getTSRole().getRoleCode().equals(areaEngineerRoleCode)) {
                return true;
            }
        }
        return false;
    }

    @Override
    public TSDepart getUserRootTsDepartCode(TSDepart depart) {
        if (depart.getOrgCode().length() != 2) {
            String parentdepartid = super.findForJdbc("SELECT parentdepartid FROM t_s_depart WHERE id = ? ", depart.getId()).get(0).get("parentdepartid").toString();
            getUserRootTsDepartCode(super.get(TSDepart.class, parentdepartid));
        }
        return depart;
    }

    @Override
    public List<Map<String, Object>> queryPlanRecord(String ipId) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ");
        sql.append(" 	 pr.id AS id,pr.create_name AS createName,pr.plan_state AS planState, DATE_FORMAT(pr.create_date,'%Y-%m-%d %H:%i:%s') AS createDate, pr.remark ");
        sql.append(" FROM ");
        sql.append(" 	zy_plan_record pr ");
        sql.append(" WHERE ");
        sql.append(" 	pr.bus_id = '").append(ipId).append("' ");
        sql.append(" ORDER BY ");
        sql.append(" 	pr.create_date DESC ");
        return super.findForJdbc(sql.toString());
    }

    @Override
    public int getCountBySql(String sql, Object... objs) {
        int total = 0;
        Map<String, Object> totalMap = super.findOneForJdbc(sql, objs);
        if (null != totalMap && StringUtil.isNotEmpty(totalMap.get("total"))) {
            total = Integer.valueOf(totalMap.get("total").toString());
        }
        return total;
    }

    @Override
    public List<String> getEngineerAuthenticationsByLigthPath(String lightPath) {
        List<String> authentication = new ArrayList<String>();
        if (lightPath.equals(StaticValue.DIC_LIGHT_PATH_02)) {
            //如果巡检计划中涉及到 光路II 那么工程师认证证书需要 DIC_CERTIFICATE_DCIC_II
            authentication.add(StaticValue.DIC_CERTIFICATE_DCAC);
            authentication.add(StaticValue.DIC_CERTIFICATE_DCIC_II);

        } else {
            //如果巡检计划中没有光路II 那么工程师认证证书 DIC_CERTIFICATE_DCIC_I 和 DIC_CERTIFICATE_DCIC_II 都可以
            authentication.add(StaticValue.DIC_CERTIFICATE_DCAC);
            authentication.add(StaticValue.DIC_CERTIFICATE_DCIC_I);
            authentication.add(StaticValue.DIC_CERTIFICATE_DCIC_II);
        }
        return authentication;
    }

    @Override
    public boolean judgeCompanyHadAreaEnginee(String company, String ligthPath) {
        if (!StringUtil.isNotEmpty(company)) {
            return false;
        }
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ");
        sql.append(" COUNT(1) as total ");
        sql.append(" FROM  ");
        sql.append(" t_s_base_user bu  ");
        sql.append(" LEFT JOIN zy_engineer eg ON eg.ts_base_user_id = bu.ID  ");
        sql.append(" LEFT JOIN t_s_user_org uo ON uo.user_id = bu.ID  ");
        sql.append(" LEFT JOIN t_s_depart dp ON dp.ID = uo.org_id  ");
        sql.append(" LEFT JOIN t_s_role_user ru ON ru.userid = bu.ID  ");
        sql.append(" LEFT JOIN t_s_role ro ON ro.ID = ru.roleid  ");
        sql.append(" WHERE  ");
        sql.append(" dp.org_code LIKE '").append(company).append("%' ");
        sql.append(" AND dp.logic_delete = 0 ");
        sql.append(" AND ro.rolecode = '").append(StaticValue.RULE_CODE_AREA_ENGINEER).append("' ");
        sql.append(" AND eg.state = '").append(StaticValue.DIC_ENGINEER_STATE_ONJOB).append("' ");
        sql.append(" AND eg.is_delete = 0 ");
        if (StringUtil.isNotEmpty(ligthPath)) {
            sql.append(" AND (");
            List<String> authentication = this.getEngineerAuthenticationsByLigthPath(ligthPath);
            for (int i = 0; i < authentication.size(); i++) {
                if (i == authentication.size() - 1) {
                    sql.append(" eg.authentication = '").append(authentication.get(i)).append("')");
                } else {
                    sql.append(" eg.authentication = '").append(authentication.get(i)).append("' OR ");
                }
            }
        }
        int count = this.getCountBySql(sql.toString());
        boolean flag = false;
        if (count > 0) {
            flag = true;
        }
        return flag;
    }

    @Override
    public void queryHomePageData(AjaxJson j, HttpServletRequest req) {
        Map<String, Object> resMap = new HashMap<String, Object>();
        TSUser user = ResourceUtil.getSessionUserName();
        List<TSRoleUser> roleUsers = systemService.findByProperty(TSRoleUser.class, "TSUser.id", user.getId());
        List<String> todoRoles = new ArrayList<String>();
        List<String> remindRoles = new ArrayList<String>();
        List<String> userRoleCodeList = new ArrayList<String>();
        for (TSRoleUser tsRoleUser : roleUsers) {
            userRoleCodeList.add(tsRoleUser.getTSRole().getRoleCode());
        }
        if (userRoleCodeList.contains(StaticValue.CBS_HD_ROLE_CODE)) {//HD
            StringBuffer sql = new StringBuffer();
            sql.append(" SELECT ");
            sql.append(" 	COUNT(1) AS total ");
            sql.append(" FROM ");
            sql.append(" 	zy_inspection_plan ip ");
            sql.append(" LEFT JOIN cbs_inspection_evaluate ie ON ie.inspection_id = ip.id ");
            sql.append(" WHERE  ");
            sql.append(" 	ie.bus_type = ? ");
            sql.append(" 	AND ie.evaluate_state = ? ");
            sql.append(" 	AND TIMESTAMPDIFF(DAY,NOW(),ip.end_time) > ? ");
            //任务提醒： HD咨询线： 巡检满意度回访超时任务提醒：巡检结束时间后超过一周而尚未进行服务评价的巡检计划数量。
            resMap.put("hd_ipHadNoIeOverTime", this.getCountBySql(sql.toString(), StaticValue.DIC_EVALUATE_BUS_TYPE_1, StaticValue.DIC_EVALUATE_STATE_0, 7));
            //代办： HD咨询线： 微信巡检服务申请待处理数量：微信认证会员提交巡检服务申请之后，尚未处理的数量。
            resMap.put("hd_wxIpServerTodo", this.getCountBySql("SELECT COUNT(1) AS total FROM cbs_inspection_appointment WHERE STATUS = ? OR STATUS = ?", "0", "4"));

            remindRoles.add("hd_ipHadNoIeOverTime");
            todoRoles.add("hd_wxIpServerTodo");
        } else if (userRoleCodeList.contains(StaticValue.RULE_CODE_BARCO_CSA)) {//CSA
            //代办： CSA： 微信认证会员申请待审核数量：未审核的微信认证会员申请。
            resMap.put("csa_wxBindUserTodo", this.getCountBySql("SELECT COUNT(1) AS total FROM cbs_wx_user_bind WHERE approval_status = ?", "0"));
            todoRoles.add("csa_wxBindUserTodo");
        } else if (userRoleCodeList.contains(StaticValue.RULE_CODE_BARCO_CSA) && userRoleCodeList.contains(StaticValue.CBS_HD_ROLE_CODE)) {//HD 和 CSA 都可以看见的
            //代办：统计未处理的采购单，状态为：待处理和处理中。HD，CSA可见
            resMap.put("hdCsa_wxProcurementTodo", this.getCountBySql("SELECT COUNT(1) AS total FROM cbs_procurement_note WHERE state = ? OR state = ?", "0", "1"));
            todoRoles.add("hdCsa_wxProcurementTodo");
        } else if (userRoleCodeList.contains(StaticValue.RULE_CODE_BARCO_SERVER_DIRECTOR)) {//巴可服务商主管
            /**
             *  未派单巡检计划数量：巡检计划没有分派给第三方的数量。
             巡检设备变更待审批数量：巡检工程师提交设备变更（增加、减少、移机）后，还未审核的变更申请数量。
             */
            /**----------------------未派单巡检计划数量------------------------↓↓↓↓↓↓-----------------------------------*/
            String states = "'" + StaticValue.DIC_PLAN_STATE_TODO + "',";
            states += "'" + StaticValue.DIC_PLAN_STATE_THIRDPARTY_REFUSED + "'";
            resMap.put("bsd_ipTodo", this.getCountBySql("SELECT COUNT(1) AS total FROM zy_inspection_plan WHERE state IN (" + states + ")"));
            /**----------------------巡检设备变更待审批数量------------------------↓↓↓↓↓↓-----------------------------------*/
            resMap.put("bsd_ipChange", this.getCountBySql("SELECT COUNT(1) AS total FROM zy_equipment_change WHERE approval_state = ?", StaticValue.DIC_EQ_REQ_STATE_NOT_APPROVAL));
            todoRoles.add("bsd_ipTodo");
            todoRoles.add("bsd_ipChange");
        } else if (userRoleCodeList.contains(StaticValue.RULE_CODE_THIRDPARTY_HEADER) && !userRoleCodeList.contains(StaticValue.RULE_CODE_AREA_ENGINEER)) {//第三方商务 并且 不是 区域工程师
            /**
             *  未派单巡检计划数量：本公司接受的巡检计划中没有分配给工程师的数量。
             巡检设备变更待审批数量：本公司的巡检工程师提交设备变更（增加、减少、移机）后，还未审核的变更申请数量。

             超时任务提醒：本公司工程师在指定的预约上门时间48小时后还未提交巡检报告的巡检计划的总数量。
             */
            TSDepart rootDepart = this.getUserRootTsDepartCode(user.getCurrentDepart());//最上级组织（公司）
            String company = rootDepart.getOrgCode();
            /**----------------------未派单巡检计划数量------------------------↓↓↓↓↓↓-----------------------------------*/
            ////未派单巡检任务数量,分两种情况查询:
            //1:巡检单位中有区域工程师的 第三放未派单数量 包括，巡检计划状态为：分派到第三方，区域工程师拒绝
            String states = "'" + StaticValue.DIC_PLAN_STATE_SEND_TO_THIRDPARTY + "',";
            states += "'" + StaticValue.DIC_PLAN_STATE_AREA_ENGINEER_REFUSED + "'";
            int ipTodos = this.getCountBySql("SELECT COUNT(1) AS total FROM zy_inspection_plan WHERE company LIKE ? AND company_had_area_eg = '1' AND state IN (" + states + ")", company + "%");
            //2:巡检单位中 没有 区域工程师的 第三放未派单数量 包括，巡检计划状态为：分派到第三方，工程师拒绝
            states = "'" + StaticValue.DIC_PLAN_STATE_SEND_TO_THIRDPARTY + "',";
            states += "'" + StaticValue.DIC_PLAN_STATE_ENGINEER_REFUSED + "'";
            ipTodos += this.getCountBySql("SELECT COUNT(1) AS total FROM zy_inspection_plan WHERE company LIKE ? AND company_had_area_eg = '0' AND state IN (" + states + ")", company + "%");
            resMap.put("th_ipTodo", ipTodos);
            /**----------------------巡检设备变更待审批数量------------------------↓↓↓↓↓↓-----------------------------------*/
            resMap.put("th_ipChange", this.getCountBySql("SELECT COUNT(1) AS total FROM zy_equipment_change WHERE inspection_company LIKE ? AND approval_state = ?", company + "%", StaticValue.DIC_EQ_REQ_STATE_NOT_APPROVAL));
            /**----------------------超时任务提醒------------------------↓↓↓↓↓↓-----------------------------------*/
            String overTimeStates = "'" + StaticValue.DIC_PLAN_STATE_INSPECTION_COMPLETE + "',";
            overTimeStates += "'" + StaticValue.DIC_PLAN_STATE_INSPECTION_CANCEL + "'";
            resMap.put("th_ipOverTime", this.getCountBySql("SELECT COUNT(1) AS total FROM zy_inspection_plan WHERE company LIKE ? AND state not in (" + overTimeStates + ") AND TIMESTAMPDIFF(HOUR ,engineer_order_time,NOW()) > 48 ", company + "%"));

            todoRoles.add("th_ipTodo");
            todoRoles.add("th_ipChange");
            remindRoles.add("th_ipOverTime");
        } else if (userRoleCodeList.contains(StaticValue.RULE_CODE_AREA_ENGINEER) && !userRoleCodeList.contains(StaticValue.RULE_CODE_THIRDPARTY_HEADER)) {// 区域工程师 并且 不是 第三方商务
            /**
             *  未接单巡检计划数量：分配给本人的巡检计划中没有接受的数量。
             * 	未派单巡检计划数量：分配给本人的巡检计划中没有分配给工程师的数量。
             未完成巡检计划数量：本人接受的巡检计划中没有提交巡检报告的数量。
             巡检设备变更待审批数量：本人提交设备变更（增加、减少、移机）后，还未审核的变更申请数量。

             超时任务提醒：第三方工程师接受的巡检计划中在指定的预约上门时间48小时后还未提交巡检报告的巡检计划的数量
             次日巡检任务提醒：本人接受的巡检计划中预约上门时间为第二天还未提交巡检报告的巡检计划的数量
             */
            ZyEngineerEntity engineer = systemService.findUniqueByProperty(ZyEngineerEntity.class, "tsBaseUser.id", user.getId());
            String engineerId = engineer.getId();
            /**----------------------未接单巡检任务数量------------------------↓↓↓↓↓↓-----------------------------------*/
            String states = "'" + StaticValue.DIC_PLAN_STATE_AREA_ENGINEER_ASSIGNED + "'";
            resMap.put("te_ipTodo", this.getCountBySql("SELECT COUNT(1) AS total FROM zy_inspection_plan WHERE aeg_id = ? AND engineer = ? AND state in (" + states + ")", engineerId, engineerId));
            /**----------------------未派单巡检计划数量------------------------↓↓↓↓↓↓-----------------------------------*/
            states = "'" + StaticValue.DIC_PLAN_STATE_THIRDPARTY_ASSIGNED + "',";
            states += "'" + StaticValue.DIC_PLAN_STATE_ENGINEER_REFUSED + "'";
            int tae_ipHadSend = this.getCountBySql("SELECT COUNT(1) AS total FROM zy_inspection_plan WHERE aeg_id = ?  AND state IN (" + states + ")", engineerId);
            resMap.put("tae_ipHadSend", tae_ipHadSend);
            /**----------------------未完成巡检任务数量------------------------↓↓↓↓↓↓-----------------------------------*/
            String didNotCompleteStates = "'" + StaticValue.DIC_PLAN_STATE_ENGINEER_ASSIGNED + "',";
            didNotCompleteStates += "'" + StaticValue.DIC_PLAN_STATE_ENGINEER_IN_IMPLEMENT + "'";
            resMap.put("te_ipDidnotComplete", this.getCountBySql("SELECT COUNT(1) AS total FROM zy_inspection_plan WHERE engineer = ? AND state in (" + didNotCompleteStates + ")", engineerId));
            /**----------------------巡检设备变更待审批数量------------------------↓↓↓↓↓↓-----------------------------------*/
            resMap.put("te_ipChange", this.getCountBySql("SELECT COUNT(1) AS total FROM zy_equipment_change WHERE create_by = ? AND approval_state = ?", user.getUserName(), StaticValue.DIC_EQ_REQ_STATE_NOT_APPROVAL));

            /**----------------------超时任务提醒------------------------↓↓↓↓↓↓-----------------------------------*/
            String overTimeStates = "'" + StaticValue.DIC_PLAN_STATE_INSPECTION_COMPLETE + "',";
            overTimeStates += "'" + StaticValue.DIC_PLAN_STATE_INSPECTION_CANCEL + "'";
            resMap.put("te_ipOverTime", this.getCountBySql("SELECT COUNT(1) AS total FROM zy_inspection_plan WHERE engineer = ? AND state not in (" + overTimeStates + ") AND TIMESTAMPDIFF(HOUR ,engineer_order_time,NOW()) > 48 ", engineerId));
            /**----------------------次日巡检任务提醒------------------------↓↓↓↓↓↓-----------------------------------*/
            resMap.put("te_ipTomorrow", this.getCountBySql("SELECT COUNT(1) AS total FROM zy_inspection_plan WHERE engineer = ? AND state not in (" + overTimeStates + ") AND TIMESTAMPDIFF(DAY ,NOW(),engineer_order_time) = 0 ", engineerId));

            todoRoles.add("te_ipTodo");
            todoRoles.add("tae_ipHadSend");
            todoRoles.add("te_ipDidnotComplete");
            todoRoles.add("te_ipChange");
            remindRoles.add("te_ipOverTime");
            remindRoles.add("te_ipTomorrow");
        } else if (userRoleCodeList.contains(StaticValue.RULE_CODE_THIRDPARTY_ENGINEER)) {//工程师
            /**
             *  未接单巡检计划数量：分配给本人的巡检计划中没有接受的数量。
             未完成巡检计划数量：本人接受的巡检计划中没有提交巡检报告的数量。
             巡检设备变更待审批数量：本人提交设备变更（增加、减少、移机）后，还未审核的变更申请数量。

             超时任务提醒：第三方工程师接受的巡检计划中在指定的预约上门时间48小时后还未提交巡检报告的巡检计划的数量
             次日巡检任务提醒：本人接受的巡检计划中预约上门时间为第二天还未提交巡检报告的巡检计划的数量
             */
            ZyEngineerEntity engineer = systemService.findUniqueByProperty(ZyEngineerEntity.class, "tsBaseUser.id", user.getId());
            String engineerId = engineer.getId();
            /**----------------------未接单巡检任务数量-----------------------↓↓↓↓↓↓-----------------------------------*/
            //未接单巡检任务数量,分两种情况查询:
            //1：巡检单位中 有区域工程师
            String states = "'" + StaticValue.DIC_PLAN_STATE_AREA_ENGINEER_ASSIGNED + "'";
            Map<String, Object> ipTodosHadAreaEg =
                    systemService.findOneForJdbc("SELECT COUNT(1) AS total FROM zy_inspection_plan WHERE engineer = ? AND company_had_area_eg = '1' AND state in (" + states + ")", engineerId);
            //2：没有区域工程师
            states = "'" + StaticValue.DIC_PLAN_STATE_THIRDPARTY_ASSIGNED + "'";
            Map<String, Object> ipTodoNoAreaEg =
                    systemService.findOneForJdbc("SELECT COUNT(1) AS total FROM zy_inspection_plan WHERE engineer = ? AND company_had_area_eg = '0' AND state in (" + states + ")", engineerId);
            int te_ipTodo = 0;
            if (null != ipTodosHadAreaEg && StringUtil.isNotEmpty(ipTodosHadAreaEg.get("total"))) {
                te_ipTodo += Integer.valueOf(ipTodosHadAreaEg.get("total").toString());
            }
            if (null != ipTodoNoAreaEg && StringUtil.isNotEmpty(ipTodoNoAreaEg.get("total"))) {
                te_ipTodo += Integer.valueOf(ipTodoNoAreaEg.get("total").toString());
            }
            resMap.put("te_ipTodo", te_ipTodo);
            /**----------------------未完成巡检任务数量-----------------------↓↓↓↓↓↓-----------------------------------*/
            String didNotCompleteStates = "'" + StaticValue.DIC_PLAN_STATE_ENGINEER_ASSIGNED + "',";
            didNotCompleteStates += "'" + StaticValue.DIC_PLAN_STATE_ENGINEER_IN_IMPLEMENT + "'";
            resMap.put("te_ipDidnotComplete", this.getCountBySql("SELECT COUNT(1) AS total FROM zy_inspection_plan WHERE engineer = ? AND state in (" + didNotCompleteStates + ")", engineerId));
            /**----------------------巡检设备变更待审批数量-----------------------↓↓↓↓↓↓-----------------------------------*/
            resMap.put("te_ipChange", this.getCountBySql("SELECT COUNT(1) AS total FROM zy_equipment_change WHERE create_by = ? AND approval_state = ?", user.getUserName(), StaticValue.DIC_EQ_REQ_STATE_NOT_APPROVAL));

            /**----------------------超时任务提醒-----------------------↓↓↓↓↓↓-----------------------------------*/
            String overTimeStates = "'" + StaticValue.DIC_PLAN_STATE_INSPECTION_COMPLETE + "',";
            overTimeStates += "'" + StaticValue.DIC_PLAN_STATE_INSPECTION_CANCEL + "'";
            resMap.put("te_ipOverTime", this.getCountBySql("SELECT COUNT(1) AS total FROM zy_inspection_plan WHERE engineer = ? AND state not in (" + overTimeStates + ") AND TIMESTAMPDIFF(HOUR ,engineer_order_time,NOW()) > 48 ", engineerId));
            /**----------------------次日巡检任务提醒----------------------↓↓↓↓↓↓-----------------------------------*/
            resMap.put("te_ipTomorrow", this.getCountBySql("SELECT COUNT(1) AS total FROM zy_inspection_plan WHERE engineer = ? AND state not in (" + overTimeStates + ") AND TIMESTAMPDIFF(DAY ,NOW(),engineer_order_time) = 0 ", engineerId));

            todoRoles.add("te_ipTodo");
            todoRoles.add("te_ipDidnotComplete");
            todoRoles.add("te_ipChange");
            remindRoles.add("te_ipOverTime");
            remindRoles.add("te_ipTomorrow");
        }
        Map<String, Object> roleMap = new HashMap<String, Object>();
        roleMap.put("todoRoles", todoRoles);
        roleMap.put("remindRoles", remindRoles);
        j.setAttributes(resMap);
        j.setObj(roleMap);
    }


    @Override
    public void queryHomePageServiceData(AjaxJson j, HttpServletRequest req) {
        Map<String, Object> resMap = new HashMap<String, Object>();
        TSUser user = ResourceUtil.getSessionUserName();
        List<TSRoleUser> roleUsers = systemService.findByProperty(TSRoleUser.class, "TSUser.id", user.getId());
        List<String> todoRoles = new ArrayList<String>();
        List<String> userRoleCodeList = new ArrayList<String>();
        for (TSRoleUser tsRoleUser : roleUsers) {
            userRoleCodeList.add(tsRoleUser.getTSRole().getRoleCode());
        }
        if (userRoleCodeList.contains(StaticValue.RULE_CODE_AREA_ENGINEER)) {//区域工程师
            //待办： 区域工程师要处理的任务数量是，派给区域工程师的且接单人是登录人的，且第三方处理状态为“第三方已分配”的服务单
            List<String> listbySql = systemService.findListbySql("select id from zy_engineer where ts_base_user_id='" + user.getId() + "'");
            resMap.put("qy_enginerTodoArea", this.getCountBySql("SELECT COUNT(1) AS total FROM cbs_service where aeg_id= ? AND speed_status = ?", listbySql.get(0), StaticValue.dsfyfp));
            todoRoles.add("qy_enginerTodoArea");
            //我的任务
            List<String> listbySql1 = systemService.findListbySql("select id from zy_engineer where ts_base_user_id='" + user.getId() + "'");
            resMap.put("qy_enginerTodoMy", this.getCountBySql("SELECT COUNT(1) AS total FROM cbs_service where engineer_id= ? AND (speed_status = ? or speed_status = ?)", listbySql.get(0), StaticValue.qygcsyfp, StaticValue.dsfyfp));
            todoRoles.add("qy_enginerTodoMy");
        } else if (userRoleCodeList.contains(StaticValue.RULE_CODE_THIRDPARTY_ENGINEER)) {//工程师
            //待办： 工程师数量统计逻辑是：仅显示分派给自己的且第三方处理状态为“区域工程师已分配”和第三方以分派的服务单
            List<String> listbySql = systemService.findListbySql("select id from zy_engineer where ts_base_user_id='" + user.getId() + "'");
            resMap.put("enginerTodo", this.getCountBySql("SELECT COUNT(1) AS total FROM cbs_service WHERE engineer_id = ? AND (speed_status = ? or speed_status = ?)", listbySql.get(0), StaticValue.qygcsyfp, StaticValue.dsfyfp));
            todoRoles.add("enginerTodo");
            //返件列表   仅显示分派给自己且工程师已经接单的
            resMap.put("enginerReturnTodo", this.getCountBySql("SELECT COUNT(1) AS total FROM cbs_return_equipment re LEFT JOIN cbs_service cbs ON cbs. CODE = re.cr_code WHERE cbs.engineer_id = ? and re.status = ? and cbs.speed_status > '4' ", listbySql.get(0), StaticValue.RETURN_STATUS_Log_Type_1));
            todoRoles.add("enginerReturnTodo");
        } else if (userRoleCodeList.contains(StaticValue.RULE_CODE_THIRDPARTY_HEADER)) {//商务
            //商务数量统计“”仅显示分派给自己的且第三方处理进度为“已分派第三方”的服务单：
            List<String> listbySql = systemService.findListbySql("select id from zy_contacts where base_id='" + user.getId() + "'");
            resMap.put("swTodo", this.getCountBySql("SELECT COUNT(1) AS total FROM cbs_service where zy_contact_id= ? AND speed_status = ?", listbySql.get(0), StaticValue.tfpdsf));
            todoRoles.add("swTodo");

        } else if (userRoleCodeList.contains(StaticValue.CBS_WL_ROLE_CODE)) {//物流
            //物流统计，显示是本角色下，发货单状态是物流待发货的数量
            String departname = user.getCurrentDepart().getDepartname();
            //物流待发货
            resMap.put("wlTodo", this.getCountBySql("select count(1) AS total from cbs_deliver WHERE `status`='" + StaticValue.wldfh1 + "' AND deliver_logistics=?", departname));
            todoRoles.add("wlTodo");
            //物流已发货
            resMap.put("wlYfhTodo", this.getCountBySql("select count(1) AS total from cbs_deliver WHERE `status`='" + StaticValue.wlyfh1 + "' AND deliver_logistics=?", departname));
            todoRoles.add("wlYfhTodo");
            //物流返件统计  显示在本角色下，返件状态是返件中的数量
            String logisticsName = user.getCurrentDepart().getDepartname();
            resMap.put("wlReturnTodo", this.getCountBySql("select count(1) AS total from cbs_return_equipment WHERE status='2' and logistics='" + logisticsName + "'"));
            todoRoles.add("wlReturnTodo");
        } else if (userRoleCodeList.contains(StaticValue.RULE_CODE_BARCO_CSA)) {//CSA
            //CSA统计，显示是本角色下，服务单责任人是本人的服务单数量
            String id = user.getId();
            resMap.put("csaTodo", this.getCountBySql("SELECT count(1) AS total from cbs_service where person_liable=? and status not in (?,?)", id, StaticValue.ywc, StaticValue.yqx));
            todoRoles.add("csaTodo");
            //CSA统计，显示是本角色下，发货单状态是CSA待发货和欠货中的发货单数量
            resMap.put("csaDelinerTodo", this.getCountBySql("select count(1) AS total from cbs_deliver WHERE status='1' OR `status`='4'"));
            todoRoles.add("csaDelinerTodo");
            //CSA统计，返件列表
            resMap.put("csaReturnTodo", this.getCountBySql("select count(1) AS total from cbs_return_equipment WHERE status='1'"));
            todoRoles.add("csaReturnTodo");
        } else if ("中影巴可".equals(user.getCurrentDepart().getShortname()) && !userRoleCodeList.contains(StaticValue.RULE_CODE_BARCO_CSA)) {
            //所有组织机构是巴可的用户，首页要显示服务单待办(因上面以显示CSA，这就不显示了)
            //显示是本角色下，服务单责任人是本人的服务单数量
            String id = user.getId();
            resMap.put("bakeTodo", this.getCountBySql("SELECT count(1) AS total from cbs_service where person_liable=? and status not in (?,?)", id, StaticValue.ywc, StaticValue.yqx));

            if (userRoleCodeList.contains(StaticValue.RULE_CODE_BARCO_CS_MANAGER)) { //巴克客服部运营经理，主要用于区别首页页面样式
                todoRoles.add("barcoCSManager");
            } else {
                todoRoles.add("bakeTodo");
            }
        }


        Map<String, Object> roleMap = new HashMap<String, Object>();
        roleMap.put("todoRoles", todoRoles);
        j.setAttributes(resMap);
        j.setObj(roleMap);
    }

    @Override
    public List<Map<String, Object>> queryTsTypesByTsTypegroupCode(String tsTypegroupCode) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ");
        sql.append("	tp.ID, tp.typecode, tp.typename");
        sql.append(" FROM ");
        sql.append(" t_s_typegroup tg ");
        sql.append(" LEFT JOIN t_s_type tp on tp.typegroupid = tg.id ");
        sql.append(" WHERE tg.typegroupcode = '").append(tsTypegroupCode).append("' ");
        return super.findForJdbc(sql.toString());
    }

    @Override
    public void queryCurrtDepartEngineer(AjaxJson j) {
        TSUser user = ResourceUtil.getSessionUserName();
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ");
        sql.append(" 	eg.id AS egId, eg.`name` AS egName ");
        sql.append(" FROM  ");
        sql.append(" 	zy_engineer eg ");
        sql.append(" WHERE ");
        sql.append(" 	eg.belong_org = '").append(user.getCurrentDepart().getId()).append("' ");
        j.setObj(super.findForJdbc(sql.toString()));
    }


//	@Override
//	public AjaxJson checkUserRandomCodeTimeout(String sessionId, String busType) {
//		AjaxJson rs = new AjaxJson();
//		boolean flag = true;//当前 seesionId 当前 业务 没有发送过验证码
//		List<CbsVerificationCodeEntity> cbsVerificationCodes = findHql("from CbsVerificationCodeEntity where sessionId =? and busType = ? order by createDate desc", sessionId,busType);
//		if(null != cbsVerificationCodes && cbsVerificationCodes.size() > 0){
//			CbsVerificationCodeEntity verificationCode = cbsVerificationCodes.get(0);
//			Integer minute = verificationCode.getTime();
//			Date createDate = verificationCode.getCreateDate();
//			Date now = new Date();
//			long t = now.getTime() - createDate.getTime();
//			long t2 = minute * 60 * 1000;
//			if(t > t2){
//				flag = true;//验证码超时
//			}else{
//				flag = false;//未超时
//				rs.setObj(verificationCode);
//			}
//		}
//		rs.setSuccess(flag);
//		return rs;
//	}

    /*
     * verifyCode可为空 （为空既是发送时的验证（sendRandomCode（）），否则是提交时的验证）
     */
    @Override
    public AjaxJson checkUserRandomCodeTimeout(String sessionId, String busType, String verifyCode) {
        AjaxJson rs = new AjaxJson();
        String message = "";
        boolean flag = true;//当前 seesionId 当前 业务 没有发送过验证码
        List<CbsVerificationCodeEntity> cbsVerificationCodes = findHql("from CbsVerificationCodeEntity where sessionId =? and busType = ? order by createDate desc", sessionId, busType);
        if (null != cbsVerificationCodes && cbsVerificationCodes.size() > 0) {
            CbsVerificationCodeEntity verificationCode = cbsVerificationCodes.get(0);
            //verifyCode判空在外边操作。这里如果参数非空才比较是否一致
            if (StringUtil.isNotEmpty(verifyCode) &&
                    !verifyCode.equals(verificationCode.getRandcode())) {
                message = "验证码不一致";
                rs.setObj(1);//特殊情况，验证码不一致时 不能重新生成新数据记录
                flag = false;
            } else {
                Integer minute = verificationCode.getTime();
                Date createDate = verificationCode.getCreateDate();
                Date now = new Date();
                long t = now.getTime() - createDate.getTime();
                long t2 = minute * 60 * 1000;
                if (t > t2) {
                    message = "验证码超时";
                    flag = false;
                } else {
                    //未超时 返回已经发送过的最新的验证码 无需再生成验证码
                    rs.setObj(verificationCode);
                }
            }
        } else {
            message = "没有发送过验证码";
            flag = false;
        }
        rs.setMsg(message);
        rs.setSuccess(flag);
        return rs;
    }

    @Override
    public AjaxJson sendRandomCode(String sessionId, String phoneNo, Integer liveTime, String templetePath, Map<String, Object> templeteMap, String busType) {
        AjaxJson j = new AjaxJson();
        if (StringUtil.isNotEmpty(sessionId) && StringUtil.isNotEmpty(phoneNo)
                && StringUtil.isNotEmpty(liveTime) && StringUtil.isNotEmpty(templetePath)
                && StringUtil.isNotEmpty(templeteMap) && StringUtil.isNotEmpty(busType)) {
            String randomCode = "";
            AjaxJson rs = checkUserRandomCodeTimeout(sessionId, busType, null);
            //验证是否验证码是否过期or发送过验证码
            if (!rs.isSuccess()) {//验证码超时，重新生成
                //验证码 默认 6位数
                String random = String.valueOf(Math.ceil(Math.random() * 500000 + 500000));
                randomCode = random.substring(0, random.indexOf("."));
            } else {//验证码未超时 无需重新生成
                randomCode = ((CbsVerificationCodeEntity) rs.getObj()).getRandcode();
            }
            System.out.println(randomCode);
            templeteMap.put("verificationCode", randomCode);//模板公共参数，验证码
            int callback = SendMessage.sendSMSFTL(phoneNo, templetePath, templeteMap, systemService);
            if (callback <= 0) {
                j.setSuccess(false);
                j.setMsg("短信发送异常");
            } else {
                if (!rs.isSuccess()) {
                    if (null == rs.getObj() || rs.getObj().equals(1)) {
                        CbsVerificationCodeEntity verification = new CbsVerificationCodeEntity();
                        verification.setSessionId(sessionId);
                        verification.setTime(liveTime);
                        verification.setRandcode(randomCode);
                        verification.setCellphone(phoneNo);
                        verification.setBusType(busType);
                        verification.setCreateDate(new Date());
                        super.save(verification);
                    }
                }
            }
        } else {
            j.setSuccess(false);
            j.setMsg("参数不正确");
        }
        return j;
    }

    public List<Map<String, Object>> sortMap(List<Map<String, Object>> maps) {
        List<Map<String, Object>> draft = new ArrayList<Map<String, Object>>();
        List<Map<String, Object>> todo = new ArrayList<Map<String, Object>>();
        List<Map<String, Object>> done = new ArrayList<Map<String, Object>>();
        for (Map<String, Object> m : maps) {
            switch (oConvertUtils.getString(m.get("handleStatus"))) {
                case "0":
                    draft.add(m);
                    break;
                case "1":
                    todo.add(m);
                    break;
                case "2":
                    done.add(m);
            }
        }
        maps.clear();
        maps.addAll(comapareMap(draft, ""));
        List<Map<String, Object>> todos = comapareMap(todo, "createTime");
        Collections.reverse(todos);
        maps.addAll(todos);
        maps.addAll(comapareMap(done, "endTime"));
        return maps;
    }

    public List<Map<String, Object>> comapareMap(List<Map<String, Object>> re, final String compareKey) {
        if (oConvertUtils.isEmpty(compareKey)) {
            return re;
        }
        Collections.sort(re, new Comparator<Map<String, Object>>() {
            @Override
            public int compare(Map<String, Object> o1, Map<String, Object> o2) {
                String v1 = oConvertUtils.getString(o1.get(compareKey));
                String v2 = oConvertUtils.getString(o2.get(compareKey));
                return v2.compareTo(v1);
            }
        });
        return re;
    }


    @Override
    public List<ComboTree> getEsTypesTreeForIrReport() {
        //"typeDesc","备件"
        String sql = "SELECT et.id AS id from `zy_equipment_type` et LEFT JOIN zy_es_item es ON et.id=es.type WHERE es.is_track='1' ";
        List<ZyEquipmentTypeEntity> territoryList = super.findByQueryString("from ZyEquipmentTypeEntity where typeDesc = '备件' ");

        List<ComboTree> comboTrees = new ArrayList<ComboTree>();
        ComboTreeModel comboTreeModel = new ComboTreeModel("id", "typeDesc", "childEquipmentTypes");
        comboTrees = super.comboTree(territoryList, comboTreeModel, null, true);
        return comboTrees;
    }

    public List<Highchart> queryFaultRateReportData(String cinemaLine, String esTypeIds, String bjTypeIds, String tb, String bp, String countDate_begin, String countDate_end) throws Exception {
        List<Highchart> highchartList = new ArrayList<>();
        DecimalFormat df = new DecimalFormat("0.0000");
        String dateSql = "";

        //获取当前年份
        Date nowDate = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM");
        String yearStr = sdf.format(nowDate);
        String year = yearStr.substring(2, 4);
        if (StringUtil.isEmpty(countDate_begin) && StringUtil.isEmpty(countDate_end)) {
            //当前年份一月份
            countDate_begin = yearStr + "-01";
            //当前月份
            countDate_end = sdf2.format(nowDate);
        }
        if (StringUtil.isNotEmpty(countDate_begin) && StringUtil.isEmpty(countDate_end)) {
            //当前月份
            countDate_end = sdf2.format(nowDate);
        }

        //获取所有放映机机型
        List<ZyEquipmentTypeEntity> territoryList = super.findByQueryString("from ZyEquipmentTypeEntity where typeDesc = '放映机' ");
        StringBuffer strAllEquipmentType = new StringBuffer(" AND type in (");
        for (ZyEquipmentTypeEntity EquipmentType : territoryList.get(0).getChildEquipmentTypes()) {
        	for (ZyEquipmentTypeEntity EquipmentType2 : EquipmentType.getChildEquipmentTypes()) {
	        	strAllEquipmentType.append("'");
	        	strAllEquipmentType.append(EquipmentType2.getId());
	        	strAllEquipmentType.append("',");
        	}
        }
        strAllEquipmentType.append("'')");
        
        //List<Map<String, Double>> azFaultRate = new ArrayList<>();//安装设备故障率
        List<Map<String, Double>> zbFaultRate = new ArrayList<>();//在保设备故障率
        //List<Map<String, Integer>> azcountList = new ArrayList<>();//安装设备数量
        List<Map<String, Integer>> azfaultcountList = new ArrayList<>();//设备故障数量
        List<Map<String, Integer>> zbcountList = new ArrayList<>();//在保设备数量
        //List<Map<String, Double>> tbAzFaultRate = new ArrayList<>();//同比安装设备故障率
        List<Map<String, Double>> tbZbFaultRate = new ArrayList<>();//同比在保设备故障率
        //统计时间段内的所有年月
        List<String> monthList = getMonthBetween(countDate_begin, countDate_end);
        for (String createDate : monthList) {

            //查询所选机型所选备件在统计期内每个月的发货量
            if (StringUtil.isEmpty(bp) && StringUtil.isEmpty(bjTypeIds) && StringUtil.isEmpty(esTypeIds) && StringUtil.isEmpty(cinemaLine)) {
                //刚进入页面，没有筛选条件，默认查询本年一月份到当前月的所有数据
            	String strDeCount  = "SELECT COUNT(*) AS count FROM `cbs_deliver_equipment` left join cbs_deliver on (deliver_id = cbs_deliver.id) "
            			+ "left join cbs_equipment_apply on (cbs_equipment_apply.id = cbs_deliver.equipment_apply_id)"
            			+"left join cbs_service on (cbs_equipment_apply.service_id = cbs_service.id and cbs_service.service_type_1 = 'feb7ca60e6f411e7a202cc3d82025d9a')  "
            			+ " WHERE cbs_deliver.deliver_type = '1' and cbs_deliver.status <> 9 and cbs_service.id is not null and date_format(cbs_deliver.create_date,'%Y-%m')=?";
                Map<String, Object> fhCountMap = super.findOneForJdbc(strDeCount, createDate);
                Object fhCountObj = fhCountMap.get("count");
                String fhCountStr = String.valueOf(fhCountObj);
                double fhCount = Double.valueOf(fhCountStr);//发货量

                /***************************同比发货量*********************************/
                Map<String, Object> tbFhCountMap = super.findOneForJdbc(strDeCount, getLastYearDate(createDate));
                Object tbFhCountObj = tbFhCountMap.get("count");
                String tbFhCountStr = String.valueOf(tbFhCountObj);
                double tbFhCount = Double.valueOf(tbFhCountStr);//同比发货量

                /********************安装设备故障率***************************/

                //查询所有状态为使用中的设备数量
//                Map<String, Object> countMap = super.findOneForJdbc("SELECT COUNT(1) as count FROM `zy_equipment_spare` WHERE server_state='1001' and date_format(install_date,'%Y-%m')<=? AND is_spare='0'", createDate);
//                double count = 0;
//                Object countObj = countMap.get("count");
//                String countStr = String.valueOf(countObj);
//                count = Double.valueOf(countStr);
//                double faultRate = 0;
//                if (count != 0) {
//                    String faultRateStr = df.format(fhCount / count);
//                    faultRate = Double.valueOf(faultRateStr);
//                }
//                Map<String, Double> azMap = new HashMap();//当月安装设备故障率
//                azMap.put(changeDate(createDate), faultRate);
//                azFaultRate.add(azMap);

                /**********************************安装设备数量***********************************/
//                Map<String, Integer> azCountsMap = new HashMap();//当月在保数量
//                int azCounts = 0;
//                if (StringUtil.isNotEmpty(countStr)) {
//                    azCounts = Integer.valueOf(countStr);
//                }
//                azCountsMap.put(changeDate(createDate), azCounts);
//                azcountList.add(azCountsMap);

                /**********************************设备故障数量***********************************/
                Map<String, Integer> azFaultCountsMap = new HashMap();//当月在保数量
                int azFaultCounts = 0;
                if (StringUtil.isNotEmpty(fhCountStr)) {
                    azFaultCounts = Integer.valueOf(fhCountStr);
                }
                azFaultCountsMap.put(changeDate(createDate), azFaultCounts);
                azfaultcountList.add(azFaultCountsMap);

                /************************************在保设备故障率********************************************/
                //获取所有状态为“使用中”的在统计月份内在保的设备的数量
                Map<String, Object> zbCountMap = super.findOneForJdbc("SELECT COUNT(1) as count FROM `zy_equipment_spare` WHERE server_state='1001' and date_format(repair_end_date,'%Y-%m')>=? and date_format(install_date,'%Y-%m')<= ? AND is_spare='0' AND bp_no is not null and bp_no <> '' and bp_no <> '200001154' "+strAllEquipmentType.toString(), createDate, createDate);
                double zbCount = 0;
                Object zbCountObj = zbCountMap.get("count");
                String zbCountStr = String.valueOf(zbCountObj);
                zbCount = Double.valueOf(zbCountStr);
                double zbDaultRate = 0;
                if (zbCount != 0) {
                    String zbFaultRateStr = df.format(fhCount / zbCount);
                    zbDaultRate = Double.valueOf(zbFaultRateStr);
                }
                Map<String, Double> zbMap = new HashMap();
                zbMap.put(changeDate(createDate), zbDaultRate);
                zbFaultRate.add(zbMap);

                /**********************************在保设备数量***********************************/
                Map<String, Integer> zbCountsMap = new HashMap();//当月在保数量
                int zbCounts = 0;
                if (StringUtil.isNotEmpty(zbCountStr)) {
                    zbCounts = Integer.valueOf(zbCountStr);
                }
                zbCountsMap.put(changeDate(createDate), zbCounts);
                zbcountList.add(zbCountsMap);

                /***********************************同比安装设备故障率*******************************/

                //查询所有状态为使用中的设备数量
//                Map<String, Object> tbCountMap = super.findOneForJdbc("SELECT COUNT(1) as count FROM `zy_equipment_spare` WHERE server_state='1001' and date_format(install_date,'%Y-%m')<=? AND is_spare='0'", getLastYearDate(createDate));
//                double tbCount = 0;
//                Object tbCountObj = tbCountMap.get("count");
//                String tbCountStr = String.valueOf(tbCountObj);
//                tbCount = Double.valueOf(tbCountStr);
//                double tbFaultRate = 0;
//                if (tbCount != 0) {
//                    String tbFaultRateStr = df.format(tbFhCount / tbCount);
//                    tbFaultRate = Double.valueOf(tbFaultRateStr);
//                }
//                Map<String, Double> tbAzMap = new HashMap();//当月安装设备故障率
//                tbAzMap.put(changeDate(createDate), tbFaultRate);
//                tbAzFaultRate.add(tbAzMap);

                /************************************同比在保设备故障率******************************/
                //获取所有状态为“使用中”的在统计月份内在保的设备的数量
                Map<String, Object> tbZbCountMap = super.findOneForJdbc("SELECT COUNT(1) as count FROM `zy_equipment_spare` WHERE server_state='1001' and date_format(repair_end_date,'%Y-%m')>=? and date_format(install_date,'%Y-%m')<= ? AND is_spare='0' AND bp_no is not null and bp_no <> '' AND bp_no <> '200001154' "+strAllEquipmentType.toString(), getLastYearDate(createDate), getLastYearDate(createDate));
                double tbZbCount = 0;
                Object tbZbCountObj = tbZbCountMap.get("count");
                String tbZbCountStr = String.valueOf(tbZbCountObj);
                tbZbCount = Double.valueOf(tbZbCountStr);
                double tbZbDaultRate = 0;
                if (tbZbCount != 0) {
                    String tbZbFaultRateStr = df.format(tbFhCount / tbZbCount);
                    tbZbDaultRate = Double.valueOf(tbZbFaultRateStr);
                }
                Map<String, Double> tbZbMap = new HashMap();
                tbZbMap.put(changeDate(createDate), tbZbDaultRate);
                tbZbFaultRate.add(tbZbMap);

            } else {
                //根据条件查询机型和备件的ID
                String typeIds = "";
                StringBuffer sql = new StringBuffer();  //页面上选择添加的自定义条件

                //筛选条件院线
                if (StringUtil.isNotEmpty(cinemaLine)) {
                    sql.append(" and cinema='" + cinemaLine + "'");
                }
                //筛选条件BP
                if (StringUtil.isNotEmpty(bp)) {
                    String bps = "";
                    if (bp.contains(",")) {
                        String[] bpArr = bp.split(",");
                        if (bpArr.length > 0) {
                            for (int i = 0; i < bpArr.length - 1; i++) {
                                bps += "'" + bpArr[i] + "',";
                            }
                            bps = bps + "'" + bpArr[bpArr.length - 1] + "'";
                        }
                        sql.append(" AND bp_no in (" + bps + ")");
                    } else {
                        sql.append(" AND bp_no ='" + bp + "'");
                    }
                }

                //筛选条件机型
                if (StringUtil.isNotEmpty(esTypeIds)) {
                    if (esTypeIds.contains(",")) {
                        String[] ids = esTypeIds.split(",");
                        if (ids.length > 0) {
                            for (int i = 0; i < ids.length - 1; i++) {
                                typeIds += "'" + ids[i] + "',";
                            }
                            typeIds = typeIds + "'" + ids[ids.length - 1] + "'";
                        }
                        sql.append(" AND type in (" + typeIds + ")");
                    } else {
                        sql.append(" AND type ='" + esTypeIds + "'");
                    }
                } else {
                	sql.append(strAllEquipmentType.toString());
                }

                //筛选条件备件类型
                String typeId = "";
                if (StringUtil.isNotEmpty(bjTypeIds)) {
                    List<Map<String, Object>> idList = new ArrayList<>();
                    if (!bjTypeIds.contains(",")) {
                        //检索备件id
                        idList = super.findForJdbc("SELECT id from zy_equipment_type where id='" + bjTypeIds + "' OR parent_id ='" + bjTypeIds + "'");
                    } else {
                        String[] bjIds = bjTypeIds.split(",");
                        typeIds = "";
                        if (bjIds.length > 0) {
                            for (int i = 0; i < bjIds.length - 1; i++) {
                                typeIds += "'" + bjIds[i] + "',";
                            }
                            typeIds = typeIds + "'" + bjIds[bjIds.length - 1] + "'";
                        }
                        idList = super.findForJdbc("SELECT id from zy_equipment_type where id IN (" + typeIds + ") OR parent_id IN (" + typeIds + ")");
                    }

                    if (idList.size() > 0) {
                        for (int i = 0; i < idList.size() - 1; i++) {
                            typeId += "'" + idList.get(i).get("id") + "',";
                        }
                        typeId = typeId + "'" + idList.get(idList.size() - 1).get("id") + "'";
                    }
                }
                StringBuffer sql2 = new StringBuffer(); //发货数量SQL
                StringBuffer sql3 = new StringBuffer(); //同比发货数量SQL
                sql2.append("SELECT COUNT(1) as count FROM  cbs_deliver_equipment left join cbs_deliver on (deliver_id = cbs_deliver.id) ");
                sql2.append(" left join cbs_equipment_apply on (cbs_equipment_apply.id = cbs_deliver.equipment_apply_id)");
                sql2.append(" left join cbs_service on (cbs_equipment_apply.service_id = cbs_service.id and cbs_service.service_type_1 = 'feb7ca60e6f411e7a202cc3d82025d9a') ");
                sql2.append(" left join zy_equipment_spare on (zy_equipment_spare.sn = cbs_service.sn) ");
                sql2.append("WHERE cbs_deliver.deliver_type = '1' and cbs_deliver.status <> 9 and cbs_service.id is not null and date_format(cbs_deliver.create_date,'%Y-%m')='" + createDate + "' ");

                sql3.append("SELECT COUNT(1) as count FROM  cbs_deliver_equipment left join cbs_deliver on (deliver_id = cbs_deliver.id) ");
                sql3.append(" left join cbs_equipment_apply on (cbs_equipment_apply.id = cbs_deliver.equipment_apply_id)");
                sql3.append(" left join cbs_service on (cbs_equipment_apply.service_id = cbs_service.id and cbs_service.service_type_1 = 'feb7ca60e6f411e7a202cc3d82025d9a') ");
                sql3.append(" left join zy_equipment_spare on (zy_equipment_spare.sn = cbs_service.sn) ");
                sql3.append("WHERE cbs_deliver.deliver_type = '1' and cbs_deliver.status <> 9 and cbs_service.id is not null and date_format(cbs_deliver.create_date,'%Y-%m')='" + getLastYearDate(createDate) + "' ");
                if (!typeId.equals("")) {
                    sql2.append(" AND type_id IN (" + typeId + ")");
                    sql3.append(" AND type_id IN (" + typeId + ")");
                }
                
                if (!sql.equals("") && sql.length() > 0) {
                    sql2.append(sql);
                    sql3.append(sql);
                }
                
                Map<String, Object> fhCountMap = super.findOneForJdbc(sql2.toString());
                Object fhCountObj = fhCountMap.get("count");
                String fhCountStr = String.valueOf(fhCountObj);
                double fhCount = Double.valueOf(fhCountStr);//发货量

                /******************同比发货量***********************/
                Map<String, Object> tbFhCountMap = super.findOneForJdbc(sql3.toString());
                Object tbFhCountObj = tbFhCountMap.get("count");
                String tbFhCountStr = String.valueOf(tbFhCountObj);
                double tbFhCount = Double.valueOf(tbFhCountStr);//同比发货量（上一年当月的发货量）

                /************************************安装设备故障率********************************************/
                //查询当前月份之前安装的状态为使用中的非备件设备数量
//                Map<String, Object> countMap = new HashMap<>();
//                if (!sql.equals("") && sql.length() > 0) {
//                    countMap = super.findOneForJdbc("SELECT COUNT(1) as count FROM `zy_equipment_spare` WHERE server_state='1001' and date_format(install_date,'%Y-%m')<='" + createDate + "' AND is_spare='0'" + sql);
//                } else {
//                    countMap = super.findOneForJdbc("SELECT COUNT(1) as count FROM `zy_equipment_spare` WHERE server_state='1001' and date_format(install_date,'%Y-%m')<='" + createDate + "' AND is_spare='0'");
//                }
//                double count = 0;
//                Object countObj = countMap.get("count");
//                String countStr = String.valueOf(countObj);
//                count = Double.valueOf(countStr);

//                double faultRate = 0;
//                if (count != 0) {
//                    String faultRateStr = df.format(fhCount / count);   //安装设备故障率 = 发货数量/使用中设备故障数量
//                    faultRate = Double.valueOf(faultRateStr);
//                }
//                Map<String, Double> azMap = new HashMap();
//                azMap.put(changeDate(createDate), faultRate);
//                azFaultRate.add(azMap);

                /**********************************安装设备数量***********************************/
//                Map<String, Integer> azCountsMap = new HashMap();//当月在保数量
//                int azCounts = 0;
//                if (StringUtil.isNotEmpty(countStr)) {
//                    azCounts = Integer.valueOf(countStr);
//                }
//                azCountsMap.put(changeDate(createDate), azCounts);
//                azcountList.add(azCountsMap);

                /**********************************设备故障数量***********************************/
                Map<String, Integer> azFaultCountsMap = new HashMap();//当月在保数量
                int azFaultCounts = 0;
                if (StringUtil.isNotEmpty(fhCountStr)) {
                    azFaultCounts = Integer.valueOf(fhCountStr);
                }
                azFaultCountsMap.put(changeDate(createDate), azFaultCounts);
                azfaultcountList.add(azFaultCountsMap);

                /************************************在保设备故障率********************************************/
                //获取筛选条件下状态为“使用中”的在统计月份内在保的设备的数量
                Map<String, Object> zbCountMap = new HashMap<>();
                if (!sql.equals("") && sql.length() > 0) {
                    zbCountMap = super.findOneForJdbc("SELECT COUNT(1) as count FROM `zy_equipment_spare` WHERE server_state='1001' and date_format(repair_end_date,'%Y-%m')>='" + createDate + "' AND is_spare='0' AND bp_no is not null and bp_no <> '' AND bp_no <> '200001154' and date_format(install_date,'%Y-%m')<='" + createDate + "' " + sql);
                } else {
                    zbCountMap = super.findOneForJdbc("SELECT COUNT(1) as count FROM `zy_equipment_spare` WHERE server_state='1001' and date_format(repair_end_date,'%Y-%m')>='" + createDate + "' AND is_spare='0' AND bp_no is not null and bp_no <> '' AND bp_no <> '200001154' and date_format(install_date,'%Y-%m')<='" + createDate + "' "+strAllEquipmentType.toString());
                }
                double zbCount = 0;
                Object zbCountObj = zbCountMap.get("count");
                String zbCountStr = String.valueOf(zbCountObj);
                zbCount = Double.valueOf(zbCountStr);
                double zbDaultRate = 0;
                if (zbCount != 0) {
                    String zbFaultRateStr = df.format(fhCount / zbCount);
                    zbDaultRate = Double.valueOf(zbFaultRateStr);
                }
                Map<String, Double> zbMap = new HashMap();
                zbMap.put(changeDate(createDate), zbDaultRate);
                zbFaultRate.add(zbMap);

                /************************************在保设备数量********************************************/
                Map<String, Integer> zbCountsMap = new HashMap();//当月在保数量
                int zbCounts = 0;
                if (StringUtil.isNotEmpty(zbCountStr)) {
                    zbCounts = Integer.valueOf(zbCountStr);
                }
                zbCountsMap.put(changeDate(createDate), zbCounts);
                zbcountList.add(zbCountsMap);

                /************************************同比安装设备故障率********************************************/
                //查询筛选条件下状态为使用中的设备数量
//                Map<String, Object> tbCountMap = new HashMap<>();
//                if (!sql.equals("") && sql.length() > 0) {
//                    tbCountMap = super.findOneForJdbc("SELECT COUNT(1) as count FROM `zy_equipment_spare` WHERE server_state='1001' and date_format(install_date,'%Y-%m')<='" + getLastYearDate(createDate) + "' AND is_spare='0' " + sql);
//                } else {
//                    tbCountMap = super.findOneForJdbc("SELECT COUNT(1) as count FROM `zy_equipment_spare` WHERE server_state='1001' and date_format(install_date,'%Y-%m')<='" + getLastYearDate(createDate) + "' AND is_spare='0' ");
//                }
//                double tbCount = 0;
//                Object tbCountObj = tbCountMap.get("count");
//                String tbCountStr = String.valueOf(tbCountObj);
//                tbCount = Double.valueOf(tbCountStr);
//                double tbFaultRate = 0;
//                if (tbCount != 0) {
//                    String faultRateStr = df.format(tbFhCount / tbCount);
//                    tbFaultRate = Double.valueOf(faultRateStr);
//                }
//                Map<String, Double> tbAzMap = new HashMap();//当月安装设备故障率
//                tbAzMap.put(changeDate(createDate), tbFaultRate);
//                tbAzFaultRate.add(tbAzMap);
                /************************************同比在保设备故障率********************************************/

                //获取筛选条件下状态为“使用中”的在统计月份内(去年当月)在保的设备的数量
                Map<String, Object> tbZbCountMap = new HashMap<>();
                if (!sql.equals("") && sql.length() > 0) {
                    tbZbCountMap = super.findOneForJdbc("SELECT COUNT(1) as count FROM `zy_equipment_spare` WHERE server_state='1001' and date_format(repair_end_date,'%Y-%m')>='" + getLastYearDate(createDate) + "' AND is_spare='0' AND bp_no is not null and bp_no <> '' AND bp_no <> '200001154' and date_format(install_date,'%Y-%m')<='" + getLastYearDate(createDate) + "' " + sql);
                } else {
                    tbZbCountMap = super.findOneForJdbc("SELECT COUNT(1) as count FROM `zy_equipment_spare` WHERE server_state='1001' and date_format(repair_end_date,'%Y-%m')>='" + getLastYearDate(createDate) + "' AND is_spare='0' AND bp_no is not null and bp_no <> '' AND bp_no <> '200001154' and date_format(install_date,'%Y-%m')<='" + getLastYearDate(createDate) + "' "+strAllEquipmentType.toString());
                }
                double tbZbCount = 0;
                Object tbZbCountObj = tbZbCountMap.get("count");
                String tbZbCountStr = String.valueOf(tbZbCountObj);
                tbZbCount = Double.valueOf(tbZbCountStr);
                double tbZbDaultRate = 0;
                if (tbZbCount != 0) {
                    String zbFaultRateStr = df.format(tbFhCount / tbZbCount);
                    tbZbDaultRate = Double.valueOf(zbFaultRateStr);
                }
                Map<String, Double> tbZbMap = new HashMap();
                tbZbMap.put(changeDate(createDate), tbZbDaultRate);
                tbZbFaultRate.add(tbZbMap);
            }
        }

        Highchart hcAzFaultCount = new Highchart();
        hcAzFaultCount.setName("设备故障数量");
        hcAzFaultCount.setData(azfaultcountList);
        hcAzFaultCount.setStack("stact1");
        highchartList.add(hcAzFaultCount);

//        Highchart hcAzCount = new Highchart();
//        hcAzCount.setName("安装设备数量");
//        hcAzCount.setData(azcountList);
//        hcAzCount.setStack("stact2");
//        highchartList.add(hcAzCount);

        Highchart hcZbCount = new Highchart();
        hcZbCount.setName("在保设备数量");
        hcZbCount.setData(zbcountList);
        hcZbCount.setStack("stact3");
        highchartList.add(hcZbCount);

//        Highchart hcTbAzFaultRate = new Highchart();
//        hcTbAzFaultRate.setName("同比安装设备故障率");
//        hcTbAzFaultRate.setData(tbAzFaultRate);
//        hcTbAzFaultRate.setStack("stact4");
//        highchartList.add(hcTbAzFaultRate);

        Highchart hcZbFaultRate = new Highchart();
        hcZbFaultRate.setName("在保设备故障率");
        hcZbFaultRate.setData(zbFaultRate);
        hcZbFaultRate.setStack("stact7");
        highchartList.add(hcZbFaultRate);
        
        Highchart hcTbZbFaultRate = new Highchart();
        hcTbZbFaultRate.setName("同比在保设备故障率");
        hcTbZbFaultRate.setData(tbZbFaultRate);
        hcTbZbFaultRate.setStack("stact5");
        highchartList.add(hcTbZbFaultRate);

//        Highchart hcAzFaultRate = new Highchart();
//        hcAzFaultRate.setName("安装设备故障率");
//        hcAzFaultRate.setData(azFaultRate);
//        hcAzFaultRate.setStack("stact6");
//        highchartList.add(hcAzFaultRate);



        return highchartList;
    }


    /*
     * 获取一个时间段内的所有年月
     * */
    public List<String> getMonthBetween(String minDate, String maxDate) throws Exception {
        ArrayList<String> result = new ArrayList<String>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");//格式化为年月

        Calendar min = Calendar.getInstance();
        Calendar max = Calendar.getInstance();

        min.setTime(sdf.parse(minDate));
        min.set(min.get(Calendar.YEAR), min.get(Calendar.MONTH), 1);

        max.setTime(sdf.parse(maxDate));
        max.set(max.get(Calendar.YEAR), max.get(Calendar.MONTH), 2);

        Calendar curr = min;
        while (curr.before(max)) {
            result.add(sdf.format(curr.getTime()));
            curr.add(Calendar.MONTH, 1);
        }

        return result;
    }

    //将yyyy-MM转化为CST yy
    public String changeDate(String dateStr) throws Exception {
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM");
        SimpleDateFormat sdf = new SimpleDateFormat("MMM-yy", Locale.ENGLISH);
        Date date = df.parse(dateStr);
        String result = sdf.format(date);

        return result;
    }

    //获取上一年的相同月份
    public String getLastYearDate(String dateStr) throws Exception {
        String result = "";
        if (StringUtil.isNotEmpty(dateStr)) {
            String yearStr = dateStr.substring(0, 4);
            int lastYear = Integer.valueOf(yearStr) - 1;
            result = lastYear + dateStr.substring(4, dateStr.length());
        }
        return result;
    }


    public void exportIrReport(String fileName, String tableData, HttpServletResponse response) throws Exception {
        OutputStream out = null;
        try {
            response.setCharacterEncoding("text/html;charset=utf-8");
            response.setContentType("application/x-msdownload;");
            response.setHeader("Content-disposition", "attachment; filename=" + (new String(fileName.getBytes("gb2312"), "ISO8859-1")));
//			response.setHeader("Content-Length", "2048");
            String sheetName = "故障率报表";
            out = response.getOutputStream();
            String[] tableDataArr = tableData.split(";");
            XSSFWorkbook xssf = new XSSFWorkbook();
            XSSFSheet sheet = xssf.createSheet(sheetName);
            int rowNum = 0;
            for (String rowData : tableDataArr) {
                XSSFRow row = sheet.createRow(rowNum);
                String[] cellDataArr = rowData.split(",");
                int cellNum = 0;
                for (String cellData : cellDataArr) {
                    XSSFCell cell = row.createCell(cellNum);
                    cell.setCellValue(cellData);
                    cellNum++;
                }
                rowNum++;
            }
            xssf.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (out != null) {
                    out.flush();
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    ;

}